package com.exam.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.exam.model.ExcelModel;
import com.exam.model.GradeModel;
import com.exam.util.Tools;

public class GradeDao extends BaseDao{

	/**
	 * 
	 * 返回错题集合 ( 1,2,3,4 )
	 * @param gid
	 * @return
	 * @throws SQLException
	 */
	public String getErrorQids(int gid) throws SQLException{
		String sql = "select errQids from ex_grade where id="+gid;
		String errQids = "";
		res = query(sql);
		if(res.next()){
			errQids = res.getString(1);
		}
		return errQids;
	}
	
	/**
	 * 删除成绩
	 * @param gid
	 * @return
	 */
	public boolean deleteGrade(int gid){
		String sql = "delete from ex_grade where id="+gid;
		return adu(sql);
	}
	
	/**
	 * 插入一条成绩 返回插入的id号
	 * @param grade
	 * @return
	 * @throws SQLException 
	 */
	public int addOneGrade(GradeModel grade) throws SQLException{
		
		int id = 0;
		
		String sql = "insert into ex_grade (" +
				"sid," +
				"pid," +
				"score," +
				"qids," +
				"errQids ) values ( " +
				"'"+grade.getSid()+"'," +
				""+grade.getPid()+"," +
				""+grade.getScore()+"," +
				"'"+grade.getQids()+"'," +
				"'"+grade.getErrQid()+"' ) ";
		if(adu(sql)){
			
			String sql1 = "select max(id) from ex_grade";
			res = query(sql1);
			if(res.next()){
				id = res.getInt(1);
			}
		}
		
		return id;
	}
	
	/**
	 * 判断是否已经有成绩 - 一个试卷只能考试一次
	 * @return
	 * @throws SQLException 
	 */
	public boolean isExistGrade(String sid ,int pid) throws SQLException{
		boolean b = false;
		String sql = " select id from ex_grade where sid='"+sid+"' and pid="+pid;
		res = query(sql);
		if(res.next()){
			b = true;
		}
		return  b;
	}
	
	/**
	 * 获取一条成绩
	 * 考试结束 直接打印成绩单
	 * @throws SQLException 
	 * 
	 */
	public GradeModel getOneGradeBySID(String sid ,int id) throws SQLException{
		GradeModel grade = new GradeModel();
		
		String sql = " select id,sid,pid,score,qids,errQids from ex_grade where sid='"+sid+"' and id="+id;
		res = query(sql);
		if(res.next()){
			grade.setId(res.getInt(1));
			grade.setSid(res.getString(2));
			grade.setPid(res.getInt(3));
			grade.setScore(res.getString(4));
			grade.setQids(Tools.decodeBase64(res.getString(5)));   //所有试题
			grade.setErrQid(Tools.decodeBase64(res.getString(6))); //错题
		}
		return grade;
	}
	
	/**
	 * 获取成绩
	 * @param sid
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public int getScore(int id) throws SQLException{
		int s = 0;
		String sql = "select score from ex_grade where  id="+id ;
		res = query(sql);
		if(res.next()){
			s = res.getInt(1);
		}
		return s;
	}
	
	
	/**
	 * 获取最近考试成绩 
	 * @param pid
	 * @param limit
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<GradeModel> getGradeListByPID(int pid,String sid,String limit) throws SQLException{
		
		String condition = " ";  //条件查询
		if(!sid.equals("")){
			condition += " and sid='"+sid+"' ";
		}

		if(pid==-1){
		 res = query("select max(id) from ex_paper");
		 if(res.next()) pid = res.getInt(1);  //获取最新试卷
		 closeDB();
		}
		
		ArrayList<GradeModel> list = new ArrayList<GradeModel>();
		GradeModel gm=null;
		String sql = " select " +
					"id," +
					"sid," +
					"( select stu_name from ex_student where stu_id=sid ) sname, " +
					"( select paper_name from ex_paper where id=pid) pname," +
					"score," +
					"errQids from ex_grade where pid="+pid+condition+limit ;
		System.out.println(sql);
		res = query(sql);
		while(res.next()){
			gm = new GradeModel();
			gm.setId(res.getInt(1));
			gm.setSid(res.getString(2));
			gm.setSname(res.getString(3));
			gm.setPname(res.getString(4));
			gm.setScore(res.getString(5));
			gm.setErrQid(Tools.decodeBase64(res.getString(6))); //错题id
			System.out.println(gm.toString());
			list.add(gm);
		}
		
		closeDB();
		return list;
	}
	
	/**
	 * 获取记录 分页用
	 * @param pid
	 * @param sid
	 * @return
	 * @throws SQLException
	 */
	public int getCount(int pid,String sid) throws SQLException{
		String condition = " ";  //条件查询
		int m=0;
		if(!sid.equals("")){
			condition += " and sid='"+sid+"' ";
		}

		if(pid==-1){
		 res = query("select max(id) from ex_paper");
		 if(res.next()) pid = res.getInt(1);  //获取最新试卷
		 closeDB();
		}
		
		String sql = "select count(*) from ex_grade  where pid="+pid+ condition;
		res = query(sql);
		if(res.next()) m = res.getInt(1);
		closeDB();
	  return m;
	}
	
	
	/**
	 * 获取所有成绩
	 */
public List<ExcelModel> getGradeList() throws SQLException{
		List<ExcelModel> list = new ArrayList<ExcelModel>();
		String sql = "SELECT\n" +
				"	s.stu_id sno,\n" +
				"	s.stu_name NAME,\n" +
				"	gg.gname clazzname,\n" +
				"	p.paper_name examName,\n" +
				"	g.score score\n" +
				"FROM\n" +
				"	ex_student s\n" +
				"JOIN ex_grade g ON s.stu_id = g.sid\n" +
				"JOIN ex_group gg ON s.gid = gg.id\n" +
				"JOIN ex_paper p on g.pid = p.id" ;
		System.out.println(sql);
		res = query(sql);
		while(res.next()){
			ExcelModel excelModel = new ExcelModel();
			excelModel.setNo(res.getString("sno"));
			excelModel.setName(res.getString("NAME"));
			excelModel.setClazzName(res.getString("clazzname"));
			excelModel.setExamName(res.getString("examName"));
			excelModel.setScore(res.getDouble("score"));
			list.add(excelModel);
		}
		closeDB();
		return list;
	}
	
	
}
